package com.youlu.campus.service.ca.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.mongodb.client.result.UpdateResult;
import com.youlu.campus.base.exception.BusinessException;
import com.youlu.campus.base.order.vo.OrderRequest;

import com.youlu.campus.common.utils.TimeUtils;
import com.youlu.campus.entity.*;
import com.youlu.campus.entity.VO.req.ReceiveCaVO;
import com.youlu.campus.entity.domain.ActivityFootPrintDomain;
import com.youlu.campus.entity.enums.TaskBizType;
import com.youlu.campus.service.activity.ActivityService;
import com.youlu.campus.service.activity.ActivityTaskRuleService;
import com.youlu.campus.service.base.LockService;
import com.youlu.campus.service.ca.CaGenerateService;
import com.youlu.campus.service.ca.CaInfoService;
import com.youlu.campus.service.ca.CaRecieveService;
import com.youlu.campus.service.cache.CacheActivityService;
import com.youlu.campus.service.file.FileExportTaskService;
import com.youlu.campus.service.file.FileService;
import com.youlu.campus.service.invite.UserInviteService;
import com.youlu.campus.service.ranking.UserActivityPointRankingService;
import com.youlu.campus.service.system.SystemOptLogService;
import com.youlu.campus.service.visitor.UserBlackListService;
import com.youlu.campus.service.visitor.VisitorUserService;
import com.youlu.campus.service.wechat.WechatConfigInfoService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.TimeUnit;
import java.util.regex.Pattern;

@Slf4j
@Service
public class CaRecieveServiceImpl implements CaRecieveService {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private ActivityService activityService;

    @Autowired
    private CaInfoService caInfoService;
    @Autowired
    private CaGenerateService caGenerateService;
    @Autowired
    private FileExportTaskService fileExportTaskService;
    @Autowired
    private SystemOptLogService systemOptLogService;
    @Autowired
    private FileService fileService;
    @Autowired
    private LockService lockService;
    private String GENERATE_CA_KEY = "com.luyou.generate.ca.activityId::%s";

    @Lazy
    @Autowired
    private VisitorUserService visitorUserService;
    @Autowired
    private ActivityTaskRuleService activityTaskRuleService;
    @Autowired
    private CacheActivityService cacheActivityService;
    @Autowired
    private UserBlackListService userBlackListService;
    @Autowired
    private WechatConfigInfoService wechatConfigInfoService;
    @Autowired
    private UserInviteService userInviteService;
    @Autowired
    private UserActivityPointRankingService userActivityPointRankingService;
    @Value("${sys.fileExportPath:/data/file/campus-admin/}")
    private String fileExportPath;

    @Override
    public ReceiveCaVO receiveCa(String activityId, String appId, UserPO userPO, Integer rewardType) {
        log.info(":>>> 开始领取证书 activityId:{},userId:{},appId: {}", activityId, userPO.getId() == null ? null : userPO.getId());


        return null;
    }

    @Override
    public boolean generateRecieveInfos(String activityId, String appId, Integer bizType, Boolean limit,
                                        String ip, String userId, String userLoginName) {
        log.info(":>>> 开始生成活动领取信息:{}", activityId);
        boolean lock = this.lockGenerCa(activityId);
        if (!lock) {
            log.error(":>>> 活动:{} 证书正在生成中", activityId);
            throw new BusinessException("证书正在生成中");
        }
        ActivityInfo activityInfo = activityService.findOne(activityId);
        if (Boolean.TRUE.equals(activityInfo.getOtherPlatform())) {
            activityInfo.setOtherPlatform(false);
            cacheActivityService.put(activityInfo);
        }
        ActivityTaskRule taskRule = activityTaskRuleService.find(activityId, TaskBizType.OFF_LINE.name());
        if (Objects.isNull(taskRule)) {
            taskRule = new ActivityTaskRule();
            taskRule.setId(null);
            taskRule.setActivityId(activityId);
            taskRule.setBizType(TaskBizType.OFF_LINE.name());
            taskRule.setThreshold(1);
            taskRule.setPoints(10.0D);
            taskRule.setCreatedTime(new Date());
            activityTaskRuleService.create(taskRule);
        }
        Query query = new Query().addCriteria(Criteria.where("activityId").is(activityId));
        query.addCriteria(Criteria.where("joined").is(true));
        query.addCriteria(Criteria.where("inBlackList").ne(true));
        Long count = mongoTemplate.count(query, UserParticipateActivityInfo.class);
        log.debug(":>>> 活动信息为:{},参与活动人员的数量", activityInfo.getName(), count);
        FileExportTask fileExportTask = new FileExportTask();
        fileExportTask.setToken(userLoginName);
        fileExportTask.setBizType("generate-ca");
        fileExportTask.setStatus(3);
        fileExportTask.setOptUser(userLoginName);
        fileExportTask.setOptUserId(userId);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());
        String fileName = "证书生成-" + (activityInfo == null ? "" : activityInfo.getName()) + "-" + dateStr;
        String f = fileName + new Date().getTime() + ".xlsx";
        fileExportTask.setFileName(f);
        fileExportTask.setNum(count.intValue());
        fileExportTask.setDesc(activityInfo == null ? "证书生成" : activityInfo.getName() + "-证书生成");
        String re = fileExportTaskService.create(fileExportTask);
        fileExportTask.setId(re);
        Query query1 = new Query().addCriteria(Criteria.where("activityId").is(activityId));
        mongoTemplate.remove(query1, UserRecieveCaOpt.class);
//        if (!Boolean.TRUE.equals(activityInfo.getStop())) {
////            log.error(":>>> 活动:{} 没有停止无法生成", activityId);
////            throw new BusinessException("没有停止无法生成");
////        }
        Query queryCaRule = new Query().addCriteria(Criteria.where("activityId").is(activityId));
        boolean exist = mongoTemplate.exists(queryCaRule, ActivityCaRule.class);
        if (!exist) {
            queryCaRule = new Query().addCriteria(Criteria.where("activityId").is("60daef1fff34156cdc96ba1d"));
            List<ActivityCaRule> caRules = mongoTemplate.find(queryCaRule, ActivityCaRule.class);
            if (!CollectionUtils.isEmpty(caRules)) {
                for (ActivityCaRule caRule : caRules) {
                    caRule.setId(null);
                    caRule.setActivityId(activityId);
                    caRule.setCreatedTime(new Date());
                    mongoTemplate.insert(caRule);
                }
            }
        }
        queryCaRule = new Query().addCriteria(Criteria.where("activityId").is(activityId));
        boolean recieveExist = mongoTemplate.exists(queryCaRule, ActivityRecieveRule.class);
        if (!recieveExist) {
            queryCaRule = new Query().addCriteria(Criteria.where("activityId").is("60daef1fff34156cdc96ba1d"));
            ActivityRecieveRule recieveRule = mongoTemplate.findOne(queryCaRule, ActivityRecieveRule.class);
            if (Objects.nonNull(recieveRule)) {
                recieveRule.setId(null);
                recieveRule.setActivityId(activityId);
                recieveRule.setCreatedTime(new Date());
                mongoTemplate.insert(recieveRule);
            }
        }
        Integer pageSize = 1000;
        Integer pages = count.intValue() / pageSize + 1;
        Integer counter = 0;
        Integer noExist = 0;
        Integer noSign = 0;
        Integer runningNum = 0;
        for (int i = 0; i < pages; i++) {
            log.info(":>>> 开始处理第:{} 页面,数量:{}", i, counter);
            if (Boolean.TRUE.equals(limit) && i > 1) {
                break;
            }
            query.limit(pageSize);
            query.skip(i * pageSize);
            List<UserParticipateActivityInfo> datas = mongoTemplate.find(query, UserParticipateActivityInfo.class);
            CompletableFuture<String> threads[] = new CompletableFuture[datas.size()];
            int j = 0;
            for (UserParticipateActivityInfo up : datas) {
                counter++;
                CompletableFuture<String> iThread = caGenerateService.generateOne(up, appId, activityId);
                threads[j] = iThread;
                j++;
                runningNum++;
                fileExportTaskService.updateRunningNum(re, 1);
            }
            CompletableFuture.allOf(threads).join();
            log.info(":>>> 结束处理第:{} 页面", i);
        }
        SystemOptLog optlog = new SystemOptLog();
        optlog.setIp(ip);
        optlog.setOptUserName(userLoginName);
        optlog.setOptTime(new Date());
        optlog.setBizType("ca-generate");
        optlog.setOptRealName(userLoginName);
        optlog.setSqlDetai(JSON.toJSONString(query, SerializerFeature.IgnoreNonFieldGetter));
        optlog.setDesc("导出证书生成文件名:" + f + ",数据总条数:" + count);
        systemOptLogService.create(optlog);
        fileExportTask.setStatus(4);
        fileExportTaskService.update(fileExportTask);
        this.exportRecieveInfos(activityId, re, null);
        this.unLockGenerCa(activityId);
        return true;
    }

    @Override
    public boolean generateOneUserCaRecieve(String activityId, String appId, Integer bizType, String mobile) {
        return false;
    }

    @Override
    public boolean exportRecieveInfos(String activityId, String fielTaskId, HttpServletResponse response) {
        log.info(":>>> 开始导出活动:{} 证书领取信息", activityId);
        Query query = new Query().addCriteria(Criteria.where("activityId").is(activityId).and("joined").is(Boolean.TRUE));
        query.addCriteria(Criteria.where("inBlackList").ne(true));
        ActivityInfo activityInfo = activityService.findOne(activityId);
        if (Objects.isNull(activityInfo)) {
            log.error(":>>> 活动不存在:{}", activityId);
            throw new BusinessException("活动不存在");
        }
        ActivityTaskInfo taskInfo = activityService.getTaskInfo(activityId, TaskBizType.INVITE);
        List<UserParticipateActivityInfo> datas = mongoTemplate.find(query, UserParticipateActivityInfo.class);
        if (CollectionUtils.isEmpty(datas)) {
            return true;
        }
        log.info(":>>> 订单总量:{}", datas.size());
        Query queryWechatConfig = new Query().addCriteria(Criteria.where("usedAuthorize").is(Boolean.TRUE));
        queryWechatConfig.with(Sort.by(
                Sort.Order.desc("priority")
        ));
        List<WechatConfigInfo> wechatConfigInfos = mongoTemplate.find(queryWechatConfig, WechatConfigInfo.class);
        if (CollectionUtils.isEmpty(wechatConfigInfos)) {
            log.error(":>>> 没有配置微信授权公众号");
            return true;
        }

        int counter = 0;
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        HashMap<String, UniversityInfo> universityInfoHashMap = new HashMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());
        String fileName = "证书领取生成记录-" + activityId + "-" + dateStr + System.currentTimeMillis();
        OutputStream os = null;
        try {
            if (Objects.nonNull(response)) {
                os = response.getOutputStream();
                this.setResponseHeader(response, fileName);
            }
            int rowNum = 1;
            int sheetNum = 1;
            //创建一个Excel表单,参数为sheet的名字
            SXSSFSheet sheet = workbook.createSheet("sheet" + "-" + sheetNum);
            setActivityUserExcelTitle(workbook, sheet);
            //创建表头
            for (UserParticipateActivityInfo content : datas) {
                String unionId = "";
                String openId = "";
                String appId = "";
                String appName = "";
                UniversityInfo universityInfo = null;
                if (StringUtils.isNotBlank(content.getUniversityId())) {
                    if (universityInfoHashMap.containsKey(content.getUniversityId())) {
                        universityInfo = universityInfoHashMap.get(content.getUniversityId());
                    } else {
                        Query query1 = new Query().addCriteria(Criteria.where("id").is(content.getUniversityId()));
                        universityInfo = mongoTemplate.findOne(query1, UniversityInfo.class);
                        universityInfoHashMap.put(content.getUniversityId(), universityInfo);
                    }
                }
                Long startQueryH5 = System.currentTimeMillis();
                Query queryH5 = new Query().addCriteria(Criteria.where("accountInfoId").is(content.getUserId()));
                List<WechatH5UserPO> h5UserPOS = mongoTemplate.find(queryH5, WechatH5UserPO.class);
                log.info(":>>> 查询账户:{}  耗时:{} 毫秒", content.getUserId(), System.currentTimeMillis() - startQueryH5);
                if (!CollectionUtils.isEmpty(h5UserPOS)) {
                    boolean to = false;
                    for (WechatConfigInfo wechatConfigInfo : wechatConfigInfos) {
                        if (to) {
                            break;
                        }
                        for (WechatH5UserPO h5UserPO : h5UserPOS) {
                            log.info(":>>> 用户appId:{},授权的appId:{}", h5UserPO.getAppId(), wechatConfigInfo.getAppId());
                            if (wechatConfigInfo.getAppId().equals(h5UserPO.getAppId())) {
                                openId = h5UserPO.getOpenId();
                                unionId = h5UserPO.getUnionId();
                                appId = h5UserPO.getAppId();
                                appName = wechatConfigInfo.getName();
                                to = true;
                                break;
                            }
                        }
                    }
                    log.info(":>>> unionId:{}", unionId);
                }
                log.info(":>>> 导出行数:{}", counter);
                if (counter != 0 && counter % 990000 == 0) {
                    sheetNum++;
                    //超过60000就自动创建一个sheet
                    sheet = workbook.createSheet("sheet-" + sheetNum);
                    setActivityUserExcelTitle(workbook, sheet);
                    //创建表头
                    //新增数据行，并且设置单元格数据
                    rowNum = 1;
                    sheetNum++;
                    log.info(":>>> 操作60000行,创建新sheet");
                }
                SXSSFRow row = sheet.createRow(rowNum);
                //
                if (StringUtils.isNotBlank(content.getName())) {
                    row.createCell(0).setCellValue(content.getName());
                }
                if (StringUtils.isNotBlank(content.getMobile())) {
                    row.createCell(1).setCellValue(content.getMobile());
                    Boolean existsMobile = userBlackListService.existsByMobile(content.getMobile());
                    if(existsMobile){
                        row.createCell(26).setCellValue("是");
                    }else{
                        row.createCell(26).setCellValue("否");
                    }

                }else{
                    row.createCell(26).setCellValue("否");
                }
                if (Objects.nonNull(universityInfo) && StringUtils.isNotBlank(universityInfo.getName())) {
                    row.createCell(3).setCellValue(universityInfo.getName());
                }
                // 报名时间
                if (Objects.nonNull(content.getSignUpTime())) {
                    String dateNowStr = TimeUtils.parse(content.getSignUpTime(), "yyyy-MM-dd HH:mm:ss");
                    row.createCell(8).setCellValue(dateNowStr);
                }
                Long startOpt = System.currentTimeMillis();
                Query query1 = new Query();
                query1.addCriteria(Criteria.where("userId").is(content.getUserId()).and("activityId").is(activityId).and("material").is(1).and("taskBizType").is(TaskBizType.INVITE));
                List<UserRecieveCaOpt> opts = mongoTemplate.find(query1, UserRecieveCaOpt.class);
                log.info(":>>> 查询用户:{} 活动:{} 证书生成:{} 毫秒", content.getUserId(),
                        activityId, System.currentTimeMillis() - startOpt);
                if (!CollectionUtils.isEmpty(opts)) {
                    UserRecieveCaOpt userRecieveCaOpt = opts.get(0);
                    if (StringUtils.isBlank(appId) && StringUtils.isNotBlank(userRecieveCaOpt.getAppId())) {
                        appId = userRecieveCaOpt.getAppId();
                    }
                    if (StringUtils.isBlank(openId) && StringUtils.isNotBlank(userRecieveCaOpt.getOpenId())) {
                        openId = userRecieveCaOpt.getOpenId();
                    }
//                    if ("contest-plus".equals(activityInfo.getType())) {
//                        UserActivityPointRanking ranking = userActivityPointRankingService.find(content.getUserId(), "", activityInfo.getId());
//                        if (Objects.nonNull(ranking) && Objects.nonNull(ranking.getQuantity())) {
//                            row.createCell(7).setCellValue(ranking.getQuantity());
//                        }
//                    } else {
//                        List<String> uidList = visitorUserService.findCorrentUserId(content.getUserId());
//                        Double q = activityService.getTaskQuantityByUids(activityInfo.getId(), taskInfo.getId(), uidList);
//                        if (Objects.nonNull(q)) {
//                            row.createCell(7).setCellValue(q.intValue());
//                        }
//                        if (Objects.nonNull(userRecieveCaOpt.getTaskData())) {
//                            if (Objects.nonNull(userRecieveCaOpt.getTaskData().getQ())) {
//                                // 贡献值
//                                row.createCell(7).setCellValue(userRecieveCaOpt.getTaskData().getQ());
//                            }
                    // 竞赛证书名字
//                        if (StringUtils.isNotBlank(userRecieveCaOpt.getTaskData().getContestCaName())) {
//                            row.createCell(17).setCellValue(userRecieveCaOpt.getTaskData().getContestCaName());
//                        }
//                        }
//                    }

                    if (Objects.nonNull(userRecieveCaOpt.getReceiveCaVO()) && Objects.nonNull(userRecieveCaOpt.getReceiveCaVO().getPaperCas())) {
                        if (userRecieveCaOpt.getReceiveCaVO().getPaperCas().containsKey(TaskBizType.INVITE.name())) {
                            if (Objects.nonNull(userRecieveCaOpt.getReceiveCaVO().getPaperCas().get(TaskBizType.INVITE.name()))) {
                                row.createCell(4).setCellValue(userRecieveCaOpt.getReceiveCaVO().getPaperCas().get(TaskBizType.INVITE.name()).getCaName());
                            }
                        }
                        //  答题证书等级
                        if (userRecieveCaOpt.getReceiveCaVO().getPaperCas().containsKey(TaskBizType.QA.name())) {
                            if (Objects.nonNull(userRecieveCaOpt.getReceiveCaVO().getPaperCas().get(TaskBizType.QA.name()))) {
                                row.createCell(25).setCellValue(userRecieveCaOpt.getReceiveCaVO().getPaperCas().get(TaskBizType.QA.name()).getCaName());
                            }
                        }
                    }
//
//                    if (Objects.nonNull(userRecieveCaOpt.getTaskData()) && Objects.nonNull(userRecieveCaOpt.getTaskData().getActivityRewardRule())) {
//                        if (Objects.nonNull(userRecieveCaOpt.getTaskData().getActivityRewardRule().getOffLineStatus())) {
//                            row.createCell(10).setCellValue(userRecieveCaOpt.getTaskData().getActivityRewardRule().getOffLineStatus());
//                        }
//                    }
//                    if (Objects.nonNull(userRecieveCaOpt.getTaskData()) && Objects.nonNull(userRecieveCaOpt.getTaskData().getOriginCaName())) {
//                        row.createCell(11).setCellValue(userRecieveCaOpt.getTaskData().getOriginCaName());
//                    }
                    // 竞赛类H5
                    if ("contest-plus".equals(activityInfo.getType())) {
                        Query queryUserQa = new Query().addCriteria(Criteria.where("userId").is(content.getUserId()).and("activityId").is(activityId));
                        List<UserQaInfo> userQaInfos = mongoTemplate.find(queryUserQa, UserQaInfo.class);
                        if (!CollectionUtils.isEmpty(userQaInfos)) {
                            for (UserQaInfo userQaInfo : userQaInfos) {
                                if (Objects.isNull(userQaInfo.getNo())) {
                                    continue;
                                }
                                switch (userQaInfo.getNo()) {
                                    case 1:
                                        row.createCell(10).setCellValue(userQaInfo.getQaScore());
                                        row.createCell(13).setCellValue(userQaInfo.getCaName());
                                        break;
                                    case 2:
                                        row.createCell(11).setCellValue(userQaInfo.getQaScore());
                                        row.createCell(14).setCellValue(userQaInfo.getCaName());
                                        break;
                                    case 3:
                                        row.createCell(12).setCellValue(userQaInfo.getQaScore());
                                        row.createCell(15).setCellValue(userQaInfo.getCaName());
                                        break;
                                    default:
                                        break;
                                }
                            }
                        }
                    } else {
                        if (Objects.nonNull(userRecieveCaOpt.getTaskData())) {
                            if (userRecieveCaOpt.getTaskData().getQaCas().size() > 0) {
                                for (Integer key : userRecieveCaOpt.getTaskData().getQaCas().keySet()) {
                                    UserQaInfo userQaInfo = userRecieveCaOpt.getTaskData().getQaCas().get(key);
                                    if (Objects.isNull(userQaInfo) && Objects.isNull(userQaInfo.getQaScore())) {
                                        continue;
                                    }
                                    switch (key) {
                                        case 1:
                                            row.createCell(10).setCellValue(userQaInfo.getQaScore());
                                            row.createCell(13).setCellValue(userQaInfo.getCaName());
                                            break;
                                        case 2:
                                            row.createCell(11).setCellValue(userQaInfo.getQaScore());
                                            row.createCell(14).setCellValue(userQaInfo.getCaName());
                                            break;
                                        case 3:
                                            row.createCell(12).setCellValue(userQaInfo.getQaScore());
                                            row.createCell(15).setCellValue(userQaInfo.getCaName());
                                            break;
                                        default:
                                            break;
                                    }
                                }
                            }
                        }
                    }

                } else {
                    log.error(":>>> 用户信息证书信息不存在:{}", content.getUserId());
                }
                if (StringUtils.isNotBlank(appId)) {
                    row.createCell(24).setCellValue(appId);
                }
                if (Objects.nonNull(appName) && StringUtils.isNotBlank(appName)) {
                    row.createCell(23).setCellValue(appName);
                }
                if (StringUtils.isNotBlank(openId)) {
                    row.createCell(2).setCellValue(openId);
                }
                if (Objects.nonNull(activityInfo) && StringUtils.isNotBlank(activityInfo.getName())) {
                    row.createCell(9).setCellValue(activityInfo.getName());
                }
                if (Objects.nonNull(content.getQaScore())) {
                    row.createCell(6).setCellValue(content.getQaScore());
                }
                if (Objects.nonNull(universityInfo) && StringUtils.isNotBlank(universityInfo.getProvince())) {
                    row.createCell(16).setCellValue(universityInfo.getProvince());
                }
                List<String> uidList = visitorUserService.findCorrentUserId(content.getUserId());
                Double q = activityService.getTaskQuantity(activityInfo.getId(), activityInfo.getType(), taskInfo.getId(), uidList);
                if (Objects.nonNull(q)) {
                    row.createCell(7).setCellValue(q.intValue());
                }
//                Query query11 = new Query().addCriteria(Criteria.where("userId").is(content.getUserId()).and("activityId").ne(activityId).and("joined").is(Boolean.TRUE));
//                Long count = mongoTemplate.count(query11, UserParticipateActivityInfo.class);
//                log.info(":>>> 用户:{} 参加其他活动次数:{}", content.getUserId(), count);
//                if (Objects.nonNull(count)) {
//                    row.createCell(17).setCellValue(count);
//                }
//                Query orderQuery = new Query().addCriteria(Criteria.where("accountInfoId").is(content.getUserId()).and("status").ne("0"));
//                List<OrderInfo> orderInfos = mongoTemplate.find(orderQuery, OrderInfo.class);
//                if (Objects.isNull(orderInfos) || CollectionUtils.isEmpty(orderInfos)) {
//                    row.createCell(18).setCellValue(0);
//                    row.createCell(19).setCellValue(0);
//                } else {
//                    row.createCell(18).setCellValue(orderInfos.size());
//                }
//                Integer gmv = 0;
//                for (OrderInfo orderInfo : orderInfos) {
//                    if (Objects.nonNull(orderInfo.getTotalFee())) {
//                        gmv = gmv + orderInfo.getTotalFee();
//                    }
//                }
//                row.createCell(19).setCellValue(gmv / 100);
                // 线下志愿活动
                if (Boolean.TRUE.equals(activityInfo.getHasOffLine())) {
                    log.info(":>>> 活动有线下活动:{}", activityInfo.getId());
                    ActivityFootPrintDomain printDomain = activityService.getActivityFootPrint(content.getUserId(), activityInfo.getId(),
                            TaskBizType.OFF_LINE.name());
                    log.info(":>>> 活动有线下活动:{},状态:{},{} ", activityInfo.getId(), content.getUserId(), JSON.toJSONString(printDomain));
                    if (Objects.nonNull(printDomain)) {
                        if (Objects.nonNull(printDomain.getTotalNum())) {
                            row.createCell(20).setCellValue(printDomain.getTotalNum());
                        }
                        if (Objects.nonNull(printDomain.getPassedNum())) {
                            row.createCell(21).setCellValue(printDomain.getPassedNum());
                        }
                    }
                }
                if (StringUtils.isNotBlank(unionId)) {
                    row.createCell(22).setCellValue(unionId);
                }
                if (!CollectionUtils.isEmpty(opts) && Objects.nonNull(opts.get(0).getTaskData())) {
                    if (Objects.nonNull(opts.get(0).getTaskData().getInvitedNum())) {
                        row.createCell(5).setCellValue(opts.get(0).getTaskData().getInvitedNum());
                    }
                } else {
                    Long startInvitedNum = System.currentTimeMillis();
                    Long invitedNum = userInviteService.getInvitedNum(content.getUserId(), activityInfo.getId());
                    log.info(":>>> 查询邀请人数耗时:{} 毫秒", (System.currentTimeMillis() - startInvitedNum));
                    if (Objects.nonNull(invitedNum)) {
                        row.createCell(5).setCellValue(invitedNum);
                    }
                }

                rowNum++;
                counter++;
                fileExportTaskService.updateExportNum(fielTaskId, 1);
            }
            //将excel写入到输出流中
            log.info(":>>> flush:{} 行", rowNum);
            OutputStream outputStream = new FileOutputStream(fileExportPath + fileName + "领取证书导出" + ".xlsx");
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            if (Objects.nonNull(os)) {
                workbook.write(os);
                os.flush();
                os.close();
            }
            String f = fileName + "领取证书导出" + ".xlsx";
            log.info(":>>> 写文件到本地然后读:{}", f);
            File file = new File(fileExportPath + f);
            InputStream in = new FileInputStream(file);
            String url = fileService.uploadFile(in, f);
            Query queryTask = new Query().addCriteria(Criteria.where("id").is(fielTaskId));
            Update update = new Update();
            update.set("url", url);
            update.set("status", 1);
            update.set("updatedTime", new Date());
            UpdateResult updateResult = mongoTemplate.updateFirst(queryTask, update, FileExportTask.class);
            log.info(":>>> 更新任务:{},状态:{}", fielTaskId, updateResult.getModifiedCount() > 0 ? true : false);
            log.info(":>>> 证书生成导出,关闭文件流");
            return true;
        } catch (Exception e) {
            log.error(":>>> 证书生成导出错误:{}", e);
        }
        return false;
    }

    @Override
    public boolean export(OrderRequest req, HttpServletResponse response) {
        log.info(":>>> 开始订单导出:{}", JSON.toJSONString(req));
        HashMap<String, UniversityInfo> universityInfoHashMap = new HashMap<>();
        Query query = this.getOrderQuery(req);
        List<OrderInfo> contents = mongoTemplate.find(query, OrderInfo.class);
        if (org.apache.commons.collections4.CollectionUtils.isEmpty(contents)) {
            log.error(":>>> 用户 订单为空");
            return false;
        }
        log.info(":>>> 订单总量:{}", contents.size());
        int counter = 0;
        HSSFWorkbook workbook = new HSSFWorkbook();
        HashMap<String, CaInfo> caInfoHashMap = new HashMap<>();
        try {
            OutputStream os = response.getOutputStream();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String dateStr = sdf.format(new Date());
            String fileName = "证书订单-" + "-" + dateStr;
            this.setResponseHeader(response, fileName);
            int rowNum = 1;
            int sheetNum = 1;
            //创建一个Excel表单,参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet" + "-" + sheetNum);
            setCaOrderExcelTitle(workbook, sheet);
            //创建表头
            for (OrderInfo content : contents) {

                Query subOrderQuery = new Query().addCriteria(Criteria.where("orderNo").is(content.getOrderNo()));
                List<OOrderInfo> subOrders = mongoTemplate.find(subOrderQuery, OOrderInfo.class);
                log.info(":>>> 开始处理订单:{},子订单数量:{}", content.getOrderNo(), subOrders.size());
                if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(subOrders)) {
                    for (OOrderInfo oOrderInfo : subOrders) {
                        CaInfo caInfo = null;
                        if (StringUtils.isNotBlank(oOrderInfo.getItemInfoId())) {
                            if (caInfoHashMap.containsKey(oOrderInfo.getItemInfoId())) {
                                caInfo = caInfoHashMap.get(oOrderInfo.getItemInfoId());
                            } else {
                                caInfo = caInfoService.findOne(oOrderInfo.getItemInfoId());
                                caInfoHashMap.put(oOrderInfo.getItemInfoId(), caInfo);
                            }

                        }
                        log.info(":>>> 导出行数:{}", counter);
                        if (counter != 0 && counter % 60000 == 0) {
                            //超过60000就自动创建一个sheet
                            sheetNum++;
                            sheet = workbook.createSheet("sheet-" + sheetNum);
                            setCaOrderExcelTitle(workbook, sheet);
                            //创建表头
                            //新增数据行，并且设置单元格数据
                            rowNum = 1;
                            sheetNum++;
                            log.info(":>>> 操作60000行,创建新sheet");
                        }
                        HSSFRow row = sheet.createRow(rowNum);
//                        if (Objects.nonNull(caInfo)) {
//                            if (StringUtils.isNotBlank(caInfo.getName())) {
//                                row.createCell(0).setCellValue(caInfo.getName());
//                            }
//                            if (StringUtils.isNotBlank(caInfo.getGoodsCode())) {
//                                row.createCell(1).setCellValue(caInfo.getGoodsCode());
//                            }
//                        }
                        if (Objects.nonNull(oOrderInfo) && StringUtils.isNotBlank(oOrderInfo.getItemTitle())) {
                            String[] splitAddress = oOrderInfo.getItemTitle().split("，");

                            if (splitAddress.length == 3) {
                                row.createCell(0).setCellValue(splitAddress[0]);
                                row.createCell(11).setCellValue(splitAddress[2]);
                                row.createCell(27).setCellValue(splitAddress[1]);

                            } else if (splitAddress.length == 2) {
                                row.createCell(0).setCellValue(splitAddress[0]);
                                row.createCell(11).setCellValue(splitAddress[1]);
                            }

//                            if (StringUtils.isNotBlank(caInfo.getGoodsCode())) {
//                                row.createCell(1).setCellValue(caInfo.getGoodsCode());
//                            }
                        }
                        // 订单编号
                        if (StringUtils.isNotBlank(content.getOrderNo())) {
                            row.createCell(2).setCellValue(content.getOrderNo());
                        }
                        // 订单状态
                        String caStatusStr = this.getOrderStatusStr(content.getStatus());
                        log.info(":>>> 订单状态:{}", caStatusStr);
                        row.createCell(3).setCellValue(caStatusStr);
                        if (StringUtils.isNotBlank(content.getLogisticsCompany())) {
                            row.createCell(4).setCellValue(content.getLogisticsCompany());
                        }
                        if (StringUtils.isNotBlank(content.getLogisticsNo())) {
                            row.createCell(5).setCellValue(content.getLogisticsNo());
                        }
                        if (StringUtils.isNotBlank(content.getActivityId())) {
                            Query queryA = new Query().addCriteria(Criteria.where("id").is(content.getActivityId()));
                            ActivityInfo activityInfo = mongoTemplate.findOne(queryA, ActivityInfo.class);
                            if (Objects.nonNull(activityInfo) && StringUtils.isNotBlank(activityInfo.getName())) {
                                row.createCell(6).setCellValue(activityInfo.getName());
                            }
                        }
                        if (StringUtils.isNotBlank(content.getUniversityName())) {
                            row.createCell(7).setCellValue(content.getUniversityName());
                        }
                        if (StringUtils.isNotBlank(content.getName())) {
                            row.createCell(8).setCellValue(content.getName());
                        }
                        if (StringUtils.isNotBlank(content.getMobile())) {
                            row.createCell(9).setCellValue(content.getMobile());
                        }
                        if (Objects.nonNull(content.getPayTime())) {
                            String dateNowStr = TimeUtils.parse(content.getPayTime(), "yyyy-MM-dd HH:mm:ss");
                            row.createCell(10).setCellValue(dateNowStr);
                        }
                        if (StringUtils.isNotBlank(content.getProvince())) {
                            row.createCell(12).setCellValue(content.getProvince());
                        }
                        if (StringUtils.isNotBlank(content.getCity())) {
                            row.createCell(13).setCellValue(content.getCity());
                        }
                        if (StringUtils.isNotBlank(content.getArea())) {
                            row.createCell(14).setCellValue(content.getArea());
                        }
                        if (StringUtils.isNotBlank(content.getAddress())) {
                            row.createCell(15).setCellValue(content.getAddress());
                        }
                        if (Objects.nonNull(oOrderInfo.getSalePrice())) {
                            row.createCell(16).setCellValue(oOrderInfo.getSalePrice() / 100);
                        }
                        if (Objects.nonNull(content.getTransactionId())) {
                            row.createCell(17).setCellValue(content.getTransactionId());
                        }
                        if (Objects.nonNull(content.getRefundFee())) {
                            row.createCell(18).setCellValue(content.getRefundFee());
                        }
//                        if (Objects.nonNull(content.getReceiveTime())) {
//                            String dateNowStr = TimeUtils.parse(content.getReceiveTime(), "yyyy-MM-dd HH:mm:ss");
//                            row.createCell(19).setCellValue(dateNowStr);
//                        }
                        if (StringUtils.isNotBlank(content.getTagsSearchStr())) {
                            row.createCell(20).setCellValue(content.getTagsSearchStr());
                        }
                        if (StringUtils.isNotBlank(content.getId())) {
                            row.createCell(21).setCellValue(content.getId());
                        }
                        if (StringUtils.isNotBlank(content.getPlatform())) {
                            row.createCell(22).setCellValue(content.getPlatform());
                        }
                        log.info(":>>> 大学ID:{}", content.getUniversityId());
                        if (StringUtils.isNotBlank(content.getUniversityId())) {
                            if (universityInfoHashMap.containsKey(content.getUniversityId())) {
                                row.createCell(23).setCellValue(universityInfoHashMap.get(content.getUniversityId()).getName());
                            } else {
                                Query query1 = new Query().addCriteria(Criteria.where("id").is(content.getUniversityId()));
                                UniversityInfo universityInfo = mongoTemplate.findOne(query1, UniversityInfo.class);
                                if (Objects.nonNull(universityInfo)) {
                                    row.createCell(23).setCellValue(universityInfo.getName());
                                    universityInfoHashMap.put(content.getUniversityId(), universityInfo);
                                }
                            }
                        }
                        if (Objects.nonNull(oOrderInfo) && StringUtils.isNotBlank(oOrderInfo.getGift())) {
                            String g = oOrderInfo.getGift().replace("<P>", "");
                            g = g.replace("</p>", "");
                            row.createCell(24).setCellValue(g);
                        }
                        if (Objects.nonNull(content.getTotalFee())) {
                            row.createCell(25).setCellValue(content.getTotalFee() / 100);
                        }
                        if (subOrders.size() > 1) {
                            row.createCell(26).setCellValue("是");
                        } else {
                            row.createCell(26).setCellValue("否");
                        }
                        // 2021-05-28 00:36:07 之后版本更新了
                        if (Objects.nonNull(caInfo) && content.getCreatedTime().after(new Date(1622133367000L))) {

                            if (StringUtils.isNotBlank(caInfo.getLevel())) {
                                row.createCell(27).setCellValue(caInfo.getLevel());
                            }
                            if (StringUtils.isNotBlank(caInfo.getEdition())) {
                                row.createCell(11).setCellValue(caInfo.getEdition());
                            }
                            if (StringUtils.isNotBlank(caInfo.getName())) {
                                row.createCell(0).setCellValue(caInfo.getName());
                            }

                        }
                        rowNum++;
                    }
                }

                counter++;
            }
            //将excel写入到输出流中
            log.info(":>>> flush:{} 行", rowNum);
            workbook.write(os);
            os.flush();
            os.close();
            log.info(":>>> 订单内容导出,关闭文件流");
            return true;
        } catch (Exception e) {
            log.error(":>>> 订单内容导出错误:{}", e);
        }
        return false;
    }

    @Override
    @Async
    public CompletableFuture<String> generateOne(UserParticipateActivityInfo up, String appId, String activityId) {
        UserPO userPO = null;
        log.info(":>>> 平台:wechat-h5");
        Query queryUser = new Query().addCriteria(Criteria.where("accountInfoId").is(up.getUserId()));
        if (StringUtils.isNotBlank(appId)) {
            queryUser.addCriteria(Criteria.where("appId").is(appId));
        }
        WechatH5UserPO wechatH5UserPO = mongoTemplate.findOne(queryUser, WechatH5UserPO.class);
        if (Objects.nonNull(wechatH5UserPO)) {
            log.error(":>>> 微信用户存在:{}", up.getUserId());
            userPO = new UserPO();
            BeanUtils.copyProperties(wechatH5UserPO, userPO);
            userPO.setId(up.getUserId());
        }
        if (Objects.isNull(userPO)) {
            log.error(":>>> 用户不存在:{}", up.getUserId());
            return CompletableFuture.completedFuture("OK");
        }
        ReceiveCaVO receiveCaVO = activityService.receiveCa(activityId, appId, userPO, 0);

        if (Boolean.FALSE.equals(receiveCaVO)) {
            return CompletableFuture.completedFuture("OK");
        }
        // 遍历纸质版证书
        for (String k : receiveCaVO.getPaperCas().keySet()) {
            log.info(":>>> 开始处理证书:{}", k);
            if (receiveCaVO.getPaperCas().containsKey(k)) {
//                    if (receiveCaVO.getPaperCas().containsKey(k) && !"暂无证书".equals(receiveCaVO.getPaperCas().get(k))) {
                UserRecieveCaOpt opt = new UserRecieveCaOpt();
                opt.setReceiveCaVO(receiveCaVO);
                opt.setActivityId(activityId);
                opt.setUserId(userPO.getId());
                switch (k) {
                    case "INVITE":
                        opt.setTaskBizType(TaskBizType.INVITE);
                        break;
                    case "QA":
                        opt.setTaskBizType(TaskBizType.QA);
                        break;
                    default:
                        break;
                }
                opt.setAppId(userPO.getAppId());
                opt.setOpenId(userPO.getOpenId());
                opt.setFrom("0");
                opt.setFromName("wechat-h5");
                opt.setStatus(0);
                opt.setCreatedTime(new Date());
                opt.setUniversityId(up.getUniversityId());
                opt.setUniversityName(up.getUniversityName());
                opt.setMobile(up.getMobile());
                opt.setTaskData(receiveCaVO.getDatas().get(TaskBizType.INVITE.name()));
                // 纸质版
                opt.setMaterial(1);
                opt.setCaGroupName(receiveCaVO.getPaperCas().get(k).getCaName());
                opt.setRecieveCaDetail(receiveCaVO.getPaperCas().get(k));
                opt.setReceiveCaVO(receiveCaVO);
                opt = mongoTemplate.insert(opt);
                log.info(":>>> 生成纸质版证书:{}", JSON.toJSONString(opt));
                // 电子版
                UserRecieveCaOpt optD = new UserRecieveCaOpt();
                optD.setActivityId(activityId);
                optD.setUserId(userPO.getId());
                switch (k) {
                    case "INVITE":
                        optD.setTaskBizType(TaskBizType.INVITE);
                        break;
                    case "QA":
                        optD.setTaskBizType(TaskBizType.QA);
                        break;
                    default:
                        break;
                }
                optD.setAppId(userPO.getAppId());
                optD.setOpenId(userPO.getOpenId());
                optD.setFrom("0");
                optD.setFromName("wechat-h5");
                optD.setStatus(0);
                optD.setCreatedTime(new Date());
                optD.setUniversityId(up.getUniversityId());
                optD.setUniversityName(up.getUniversityName());
                optD.setMobile(up.getMobile());
                optD.setTaskData(receiveCaVO.getDatas().get(TaskBizType.INVITE.name()));
                // 电子版
                optD.setMaterial(0);
                optD.setCaGroupName(receiveCaVO.getPaperCas().get(k).getCaName());
                optD.setRecieveCaDetail(receiveCaVO.getPaperCas().get(k));
                optD.setReceiveCaVO(receiveCaVO);
                optD = mongoTemplate.insert(optD);
                log.info(":>>> 生成电子版证书:{}", JSON.toJSONString(optD));
            }

        }
        return CompletableFuture.completedFuture("OK");
    }


    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes());
            response.reset();
            response.setContentType("application/msexcel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            log.error(":>>> setResponseHeader error:{}", ex);
        }
    }

    private Query getOrderQuery(OrderRequest request) {
        Query query = new Query();
        if (StringUtils.isNotBlank(request.getStatus())) {
            query.addCriteria(Criteria.where("status").is(request.getStatus()));
        }
        if (StringUtils.isNotBlank(request.getActivityId())) {
            query.addCriteria(Criteria.where("activityId").is(request.getActivityId()));
        }
        if (StringUtils.isNotBlank(request.getUniversityId())) {
            query.addCriteria(Criteria.where("universityId").is(request.getUniversityId()));
        }
        if (StringUtils.isNotBlank(request.getOrderNo())) {
            query.addCriteria(Criteria.where("orderNo").is(request.getOrderNo()));
        }
        if (StringUtils.isNotBlank(request.getMobile())) {
            Pattern pattern = Pattern.compile("^.*" + request.getMobile() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("mobile").regex(pattern));
        }
        if (StringUtils.isNotBlank(request.getName())) {
            Pattern pattern = Pattern.compile("^.*" + request.getName() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("name").regex(pattern));
        }
        if (Objects.nonNull(request.getVerifyStatus())) {
            query.addCriteria(Criteria.where("verifyStatus").is(request.getVerifyStatus()));
        }
        if (StringUtils.isNotBlank(request.getTags())) {
            Pattern pattern = Pattern.compile("^.*" + request.getTags() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("tagsSearchStr").regex(pattern));
        }
        if (request.getStartTime() != null || request.getEndTime() != null) {
            Criteria criteria = Criteria.where("payTime");
            if (request.getStartTime() != null) {
                criteria.gte(request.getStartTime());
            }
            if (request.getEndTime() != null) {
                criteria.lte(request.getEndTime());
            }
            query.addCriteria(criteria);
        }
        return query;
    }

    private void setActivityUserExcelTitle(SXSSFWorkbook workbook, SXSSFSheet sheet) {
        SXSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);

        //设置为居中加粗

        SXSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("姓名");
//        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("手机号");


        cell = row.createCell(2);
        cell.setCellValue("OpenId");


        cell = row.createCell(3);
        cell.setCellValue("学校");


        cell = row.createCell(4);
        cell.setCellValue("志愿证书等级");


        cell = row.createCell(5);
        cell.setCellValue("邀请人数");

        cell = row.createCell(6);
        cell.setCellValue("考试得分");

        cell = row.createCell(7);
        cell.setCellValue("贡献值");

        cell = row.createCell(8);
        cell.setCellValue("报名时间");

        cell = row.createCell(9);
        cell.setCellValue("活动名称");

        cell = row.createCell(10);
        cell.setCellValue("校赛得分");

        cell = row.createCell(11);
        cell.setCellValue("省赛得分");

        cell = row.createCell(12);
        cell.setCellValue("国赛得分");

        cell = row.createCell(13);
        cell.setCellValue("校赛证书");

        cell = row.createCell(14);
        cell.setCellValue("省赛证书");

        cell = row.createCell(15);
        cell.setCellValue("国赛证书");

        cell = row.createCell(16);
        cell.setCellValue("省");

        cell = row.createCell(17);
        cell.setCellValue("参加活动次数");

        cell = row.createCell(18);
        cell.setCellValue("购买次数");

        cell = row.createCell(19);
        cell.setCellValue("购买总GMV");
        cell = row.createCell(20);
        cell.setCellValue("志愿服务图片总张数");
        cell = row.createCell(21);
        cell.setCellValue("志愿服务图片审核通过张数");
        cell = row.createCell(22);
        cell.setCellValue("UnionId");
        cell = row.createCell(23);
        cell.setCellValue("公众号名称");
        cell = row.createCell(24);
        cell.setCellValue("公众号AppId");
        cell = row.createCell(25);
        cell.setCellValue("答题证书等级");
        cell = row.createCell(26);
        cell.setCellValue("是否在黑名单");

    }

    /**
     * 设置订单状态字符串
     *
     * @param caStatus
     * @return
     */
    private String getOrderStatusStr(String caStatus) {
        String status = " ";
        if (Objects.nonNull(caStatus)) {

            switch (caStatus) {
                case "0":
                    status = "待付款";
                    break;
                case "1":
                    status = "已付款";
                    break;
                case "2":
                    status = "制作中";
                    break;
                case "3":
                    status = "待发货";
                    break;
                case "4":
                    status = "待收货";
                    break;
                case "5":
                    status = "已收货";
                    break;
                case "6":
                    status = "订单完成";
                    break;
                case "7":
                    status = "订单关闭";
                    break;
                case "8":
                    status = "售后中";
                    break;
                case "9":
                    status = "退款中";
                    break;
                case "10":
                    status = "待返货";
                    break;
                case "11":
                    status = "返货中";
                    break;
                case "12":
                    status = "退款完成";
                    break;
                default:
                    break;
            }
        }
        return status;
    }

    private void setCaOrderExcelTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("证书名");
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue("证书编号");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("订单编号");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("订单状态");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("物流公司");
        cell.setCellStyle(style);

        cell = row.createCell(5);
        cell.setCellValue("物流编号");
        cell.setCellStyle(style);

        cell = row.createCell(6);
        cell.setCellValue("活动名称");
        cell.setCellStyle(style);

        cell = row.createCell(7);
        cell.setCellValue("大学");
        cell.setCellStyle(style);

        cell = row.createCell(8);
        cell.setCellValue("姓名");
        cell.setCellStyle(style);

        cell = row.createCell(9);
        cell.setCellValue("电话");
        cell.setCellStyle(style);

        cell = row.createCell(10);
        cell.setCellValue("支付时间");
        cell.setCellStyle(style);

        cell = row.createCell(11);
        cell.setCellValue("证书材质");
        cell.setCellStyle(style);
        cell = row.createCell(12);
        cell.setCellValue("省");
        cell.setCellStyle(style);
        cell = row.createCell(13);
        cell.setCellValue("市");
        cell.setCellStyle(style);
        cell = row.createCell(14);
        cell.setCellValue("区");
        cell.setCellStyle(style);
        cell = row.createCell(15);
        cell.setCellValue("地址");
        cell.setCellStyle(style);
        cell = row.createCell(16);
        cell.setCellValue("单价");
        cell.setCellStyle(style);
        cell = row.createCell(17);
        cell.setCellValue("微信交易号");
        cell.setCellStyle(style);
        cell = row.createCell(18);
        cell.setCellValue("退款金额");
        cell.setCellStyle(style);
        cell = row.createCell(19);
        cell.setCellValue("退款时间");
        cell.setCellStyle(style);
        cell = row.createCell(20);
        cell.setCellValue("订单备注");
        cell.setCellStyle(style);
        cell = row.createCell(21);
        cell.setCellValue("订单标示");
        cell.setCellStyle(style);
        cell = row.createCell(22);
        cell.setCellValue("平台");
        cell.setCellStyle(style);
        cell = row.createCell(23);
        cell.setCellValue("大学");
        cell.setCellStyle(style);
        cell = row.createCell(24);
        cell.setCellValue("礼品");
        cell.setCellStyle(style);
        cell = row.createCell(25);
        cell.setCellValue("订单总价");
        cell.setCellStyle(style);
        cell = row.createCell(26);
        cell.setCellValue("是否需要合单");
        cell.setCellStyle(style);
        cell = row.createCell(27);
        cell.setCellValue("等级");
        cell.setCellStyle(style);

    }

    private boolean lockGenerCa(String activityId) {
        return lockService.tryLock(getGenerCaKey(activityId), 60, TimeUnit.SECONDS);
    }

    private String getGenerCaKey(String activityId) {

        return String.format(GENERATE_CA_KEY, activityId);
    }

    private boolean unLockGenerCa(String activityId) {
        String key = getGenerCaKey(activityId);
        lockService.unLock(key);
        return true;
    }

}
